﻿
CREATE PROCEDURE [dbo].[exbug_ListaIndicatoriStructuri]
	-- Add the parameters for the stored procedure here
	@startDate datetime, 
	@endDate datetime,
	@CodUnitate smallint
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @SupTotal decimal(18,2)
	Select @SupTotal = SUM(suprafata) FROM Departamente where IDTipDepartament in (16,18,22) AND IDDepartParinte = 0 AND Activ=1 AND ExecutieCheltuieli=1
	

	SELECT d.Nume NumeDepartament,d.IDTipDepartament,Sum(ecp.Internati) NrPacienti, SUM(ecp.ZileSpitalizare) ZileSpitalizare,
	Sum(d.NumericField) NrPaturi,
	Sum(ec.Valoare+COalesce(ecsub.Valoare_Sub,0)) Cheltuieli_Totale,
	MAX(d.Suprafata) Suprafata, 
	SUM(Valoare_Indirecta)*MAX(Suprafata)/@SupTotal Chelt_indirecte,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(ec.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(ec.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(ec.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(ZileSpitalizare)/Sum(d.NumericField) ELSE 0 END UtilizarePaturi,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(ZileSpitalizare)/Sum(ecp.Internati) ELSE 0 END DurataSpitalizare,
	COALESCE(Sum(d.NumericField),0) Nr_Paturi,
	COALESCE(Sum(ecp.Internati),0) Internati,
	COALESCE(Sum(ecp.ZileSpitalizare),0) Zile_Spitalizare
	FROM Departamente d 
	left outer JOIN 
	(
	SELECT IDDepartament,CodUnitate,Data,IDJurnal,SUM(Valoare) Valoare FROM ExecutieCheltuieli
	WHERE CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	GROUP BY IDDepartament,CodUnitate,Data,IDjurnal
	) ec  ON ec.IDDepartament = d.IDDepartament and ec.CodUnitate = d.CodUnitate 
	left outer JOIN 
	(
	SELECT a1.CodUnitate,Data,a1.IDJurnal,SUM(Valoare) Valoare_Indirecta FROM ExecutieCheltuieli a1 join
	Departamente dd1 ON a1.CodUnitate = dd1.CodUnitate and a1.IDDepartament = dd1.IDDepartament
	WHERE a1.CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	and dd1.IDTipDepartament not in (16,18,22)
	GROUP BY a1.CodUnitate,Data,a1.IDjurnal
	) ecind  ON ecind.Data = ec.Data and ecind.CodUnitate = ec.CodUnitate 
	
	LEFT OUTER JOIN 
	(
	 SELECT IDDepartParinte,a1.CodUnitate,Data,a1.IDJurnal,SUM(Valoare) Valoare_Sub FROM ExecutieCheltuieli a1 JOIN Departamente d1 ON a1.CodUnitate = d1.CodUnitate and a1.IDDepartament = d1.IDDepartament
	 WHERE a1.CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	 GROUP BY IDDepartParinte,a1.CodUnitate,Data,a1.IDjurnal
	 ) ecsub  ON ecsub.IDDepartParinte = ec.IDDepartament and ecsub.CodUnitate = ec.CodUnitate and ecsub.Data = ec.Data
	 
	 
	LEFT OUTER JOIN 
	(
	 SELECT e.IDDepartament,e.IDJurnal,e.Data,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate 
	 GROUP BY e.IDDepartament,e.IDJurnal,e.Data,e.CodUnitate
	) ecp ON ec.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=ec.CodUnitate and ecp.Data = ec.Data AND ec.IDJurnal=ecp.IDJurnal
	
	WHERE ec.CodUnitate = @CodUnitate AND 
	YEAR(ec.Data)*100+MONTH(ec.Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(ec.Data)*100+MONTH(ec.Data) <= YEAR(@endDate)*100+MONTH(@endDate) AND
	d.IDTipDepartament in (16,18,22) 
	AND d.IDDepartParinte = 0 AND d.Activ=1 AND d.ExecutieCheltuieli=1
	GROUP BY d.Nume,d.IDTipDepartament
	ORDER BY d.IDTipDepartament,d.Nume

END